﻿using RndMaterialDisposalService.Attrbutes;
using RndMaterialDisposalService.Models;
using RndMaterialDisposalService.Models.RequestModel;
using RndMaterialDisposalService.Repository;
using RndMaterialDisposalService.Services.Interface.OutsourcingProcessing;
using Microsoft.Data.SqlClient;
using Newtonsoft.Json;
using System.Web;

namespace RndMaterialDisposalService.Services.OutsourcingProcessing
{
    [NAutowired(Lifecycle.Scoped)]
    public class OutsourcingProcessingService: IOutsourcingProcessingService
    {
        private readonly AssetsManagementContext dbContext;
        private readonly UserBaseInforDataContext userBaseInforDataContext;
        private readonly WorkflowCommonDataContext workflowCommonDataContext;
        public OutsourcingProcessingService(AssetsManagementContext _dbContext, UserBaseInforDataContext _userBaseInforDataContext, WorkflowCommonDataContext _workflowCommonDataContext)
        {
            dbContext = _dbContext;
            userBaseInforDataContext = _userBaseInforDataContext;
            workflowCommonDataContext = _workflowCommonDataContext;
        }

        /// <summary>
        /// 获取主流程填写环节数据
        /// </summary>
        /// <param name="ProcInstID"></param>
        /// <returns></returns>
        public async Task<TheMainFlowFormDataModel> TheMainFlowFormData(string ProcInstID) 
        {
            TheMainFlowFormDataModel theMainFlowFormData = new TheMainFlowFormDataModel();
            if (!string.IsNullOrWhiteSpace(ProcInstID)) 
            {
                var item = await workflowCommonDataContext.ApplicationData.FirstOrDefaultAsync(a => a.ProcInstId == ProcInstID);
                if (item != null) 
                {
                    dynamic dyn = JsonConvert.DeserializeObject<dynamic>(item.FormData);
                    theMainFlowFormData.trialApplication = JsonConvert.DeserializeObject<TrialApplicationModel>(dyn.trialApplication.ToString());
                    theMainFlowFormData.determineTrackMaterialTracking = JsonConvert.DeserializeObject<List<TrackMaterialTrackingDetails>>(dyn.task1.determineTrackMaterialTrackingList.ToString());
                    theMainFlowFormData.materialTrackingDetailsList = JsonConvert.DeserializeObject<List<MaterialTrackingDetails>>(dyn.materialTrackingDetailsList.ToString());
                }
            }
            return theMainFlowFormData;
        }

        /// <summary>
        /// 新增外协加工电子流业务数据
        /// </summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        public async Task<int> AddOutsourcingProcessingBusinessData(OutsourcingProcessingBusinessDataModel Input) 
        {
            var item = await dbContext.OutsourcingProcessingBusinessData.FirstOrDefaultAsync(a => a.ProcInstId == Input.ProcInstId);
            if (item != null)
            {
                dbContext.OutsourcingProcessingBusinessData.Remove(item);
            }

            dbContext.OutsourcingProcessingBusinessData.Add(Input);
            return await dbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 返回外协加工电子流报表数据（带分页）
        /// </summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        public List<OutsourcingProcessingReportFormModel> GetOutsourcingProcessingReportFormPageList(OutsourcingProcessingReportConditionModel Input) 
        {
            List<OutsourcingProcessingReportFormModel> list = new List<OutsourcingProcessingReportFormModel>();
            if (Input != null) 
            {
                string condition = $"1=1 AND V.FatherProcInstId ='{Input.FatherProcInstId}' ";
                if (!string.IsNullOrWhiteSpace(Input.OutApplicationNumber)) 
                {
                    condition += $"AND V.ApplicationNumber LIKE '%{Input.OutApplicationNumber}%' ";
                }
                if (!string.IsNullOrWhiteSpace(Input.OutApplicantTime)) 
                {
                    string[] timeArr = Input.OutApplicantTime.Split(',');
                    if (timeArr.Length > 0)
                    {
                        condition += $"AND V.CreateDate >= '{timeArr[0]} 00:00:00' AND V.CreateDate <= '{timeArr[1]} 23:59:59' ";
                    }
                }
                if (!string.IsNullOrWhiteSpace(Input.OutSubmitBy)) 
                {
                    //当前用户工号
                    string employeeNumber = Input.OutSubmitBy.Substring(0, Input.OutSubmitBy.IndexOf(" "));
                    condition += $"AND V.ApplicantEmployeeNumber = '{employeeNumber}' ";
                }
                if (!string.IsNullOrWhiteSpace(Input.OutCurrentStatus)) 
                {
                    condition += $"AND V.CurrentStatus = '{Input.OutCurrentStatus}' ";
                }

                int startIndex = ((Input.pageIndex - 1) * Input.pageSize) + 1;
                int endIndex = Input.pageSize * Input.pageIndex;

                string sql = @$"SELECT V.[Key],V.ApplicationNumber,V.ProcInstId,V.ApplicantEmployeeNumber,V.ApplicantName,V.CurrentStatus, 
                                   V.ProcessingType,V.TaskOrderPO,V.TaskOrderCount,V.TaskProductCode,V.IsMaterialKitting,
                                   V.CreateDate,V.FatherProcInstId
                                   
                            FROM (SELECT ROW_NUMBER() OVER(ORDER BY V.CreateDate DESC) AS RowID,V.* FROM (
                            SELECT a.SysId AS [Key],TRIM(workflow.ApplicationNumber)AS ApplicationNumber,workflow.ApplicantEmployeeNumber,workflow.ApplicantName,workflow.CurrentStatus,
                                   a.ProcInstId,a.ProcessingType,a.TaskOrderPO,a.TaskOrderCount,a.TaskProductCode,a.IsMaterialKitting,a.CreateDate,a.FatherProcInstId
                                  
                            FROM AssetsManagement.dbo.OutsourcingProcessingBusinessData a  
                            LEFT JOIN VW_Common.dbo.WorkflowApplicationData workflow ON a.ProcInstId = workflow.ProcInstId) V WHERE {condition}) V 
                            WHERE V.RowID BETWEEN {startIndex} AND {endIndex}";

                list = SqlQuery<OutsourcingProcessingReportFormModel>(sql, new List<SqlParameter>());
            }
            return list;
        }

        /// <summary>
        ///返回外协加工电子流报表主数据
        /// </summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        public List<OutsourcingProcessingReportFormModel> GetOutsourcingProcessingReportFormData(OutsourcingProcessingReportConditionModel Input) 
        {
            List<OutsourcingProcessingReportFormModel> list = new List<OutsourcingProcessingReportFormModel>();
            if (Input != null)
            {
                string condition = $"1=1 AND a.FatherProcInstId ='{Input.FatherProcInstId}' ";
                if (!string.IsNullOrWhiteSpace(Input.OutApplicationNumber))
                {
                    condition += $"AND workflow.ApplicationNumber LIKE '%{Input.OutApplicationNumber}%' ";
                }
                if (!string.IsNullOrWhiteSpace(Input.OutApplicantTime))
                {
                    string[] timeArr = Input.OutApplicantTime.Split(',');
                    if (timeArr.Length > 0)
                    {
                        condition += $"AND a.CreateDate >= '{timeArr[0]} 00:00:00' AND a.CreateDate <= '{timeArr[1]} 23:59:59' ";
                    }
                }
                if (!string.IsNullOrWhiteSpace(Input.OutSubmitBy))
                {
                    //当前用户工号
                    string employeeNumber = Input.OutSubmitBy.Substring(0, Input.OutSubmitBy.IndexOf(" "));
                    condition += $"AND workflow.ApplicantEmployeeNumber = '{employeeNumber}' ";
                }
                if (!string.IsNullOrWhiteSpace(Input.OutCurrentStatus))
                {
                    condition += $"AND workflow.CurrentStatus = '{Input.OutCurrentStatus}' ";
                }
       

                string sql = @$"SELECT a.SysId AS [Key],TRIM(workflow.ApplicationNumber)AS ApplicationNumber,workflow.ApplicantEmployeeNumber,workflow.ApplicantName,workflow.CurrentStatus,
                                   a.ProcInstId,a.ProcessingType,a.TaskOrderPO,a.TaskOrderCount,a.TaskProductCode,a.IsMaterialKitting,a.CreateDate,a.FatherProcInstId

                            FROM AssetsManagement.dbo.OutsourcingProcessingBusinessData a 
                            LEFT JOIN VW_Common.dbo.WorkflowApplicationData workflow ON a.ProcInstId = workflow.ProcInstId 
                            WHERE {condition}";

                list = SqlQuery<OutsourcingProcessingReportFormModel>(sql, new List<SqlParameter>());
            }
            return list;
        }

        /// <summary>
        /// 返回打印表单数据
        /// </summary>
        /// <param name="ProcInstID"></param>
        /// <returns></returns>
        public async Task<PrintFormDataModel> GetObtainPrintFormData(string ProcInstID) 
        {
            PrintFormDataModel PrintFormData = new PrintFormDataModel();
            if (!string.IsNullOrWhiteSpace(ProcInstID)) 
            {
                var item = await dbContext.OutsourcingProcessingBusinessData.FirstOrDefaultAsync(a => a.ProcInstId == ProcInstID);
                if (item != null) 
                {
                    PrintFormData.TaskOrderPO = item.TaskOrderPO;
                    PrintFormData.ApplicationNumber = item.ProcessFolio;
                    PrintFormData.RawMaterialCode = item.RawMaterialCode;
                    PrintFormData.RawProductType = item.RawProductType;
                    PrintFormData.RawManufacturer = item.RawManufacturer;
                    PrintFormData.NewMaterialCode = item.NewMaterialCode;
                    PrintFormData.NewProductType = item.NewProductType;
                    PrintFormData.NewManufacturer = item.NewManufacturer;
                    PrintFormData.TrialBoardCabinetCode = item.TrialBoardCabinetCode;
                    PrintFormData.SingleBoardCabinetType = item.SingleBoardCabinetType;
                    PrintFormData.TrialFinishedProductCount = item.TrialFinishedProductCount;
                    PrintFormData.ElementPositioning = item.ElementPositioning;
                    PrintFormData.ProcessingType = item.ProcessingType;
                }
            }
            return PrintFormData;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="sqlParameters"></param>
        /// <returns></returns>
        public List<T> SqlQuery<T>(string sql, List<SqlParameter> sqlParameters) where T : class, new()
        {
            List<T> list = new List<T>();
            var conn = dbContext.Database.GetDbConnection();
            var comm = conn.CreateCommand();
            try
            {
                conn.Open();
                comm.CommandText = sql;
                if (sqlParameters.Count > 0)
                {
                    sqlParameters.ForEach((x) => {
                        comm.Parameters.Add(x);
                    });
                }

                var dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    T t = new T();
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        var columnName = dr.GetName(i);
                        var colDataType = dr.GetFieldType(i);
                        var item = dr.GetFieldValue<object>(i);
                        if (item == DBNull.Value)
                        {
                            item = null;
                        }
                        //var fieid = t.GetType().GetField(columnName);
                        //fieid?.SetValue(t, item);
                        var property = t.GetType().GetProperty(columnName);
                        property?.SetValue(t, item);
                    }
                    list.Add(t);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conn != null && conn.State != System.Data.ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return list;
        }
    }
}
